package biz.bbtec.sbm.client.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import biz.bbtec.sbm.client.bean.BusinessBean;
import biz.bbtec.sbm.client.bean.NoteBean;
import biz.bbtec.sbm.db.dbConnection;
import biz.bbtec.sbm.user.bean.UserBean;

import com.opensymphony.xwork2.ActionContext;

public class NoteService {
	dbConnection db = new dbConnection();

	public boolean ModifyNote(NoteBean note) {
		boolean flag = false;
		String sql = "update note set title=? , text=? where noteid= " + "'"
				+ note.getNoteid() + "'";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			past.setString(1, note.getTitle());
			past.setString(2, note.getText());
			past.executeUpdate();
			past.close();
			flag = true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public boolean DeleteNote(NoteBean note) throws SQLException {
		boolean flag = false;
		Connection conn = db.getConnection();
		String sql1 = "delete from note where noteid =" + "'"
				+ note.getNoteid() + "'";
		
		try {
			PreparedStatement past = null;
			past = conn.prepareStatement(sql1);
			past.executeUpdate(sql1);
			flag = true;
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public boolean AddNote(NoteBean notebean, String filename)
			throws ParseException, SQLException {

		boolean flag = false;

		Map session = ActionContext.getContext().getSession();
		int bizid = (Integer) session.get("bizid");
		int clientid = (Integer) session.get("clientid");
		UserBean user = (UserBean) session.get("user");
		int userid = user.getUserid();

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
		Date date = new Date();
		String thisDate = sdf.format(date);
		System.out.println(thisDate);
		Connection conn = db.getConnection();
		PreparedStatement past = null;

		String sql1 = "insert into note(title,text,userid,bizid,clientid,date) values("
				+ "'"
				+ notebean.getTitle()
				+ "'"
				+ ","
				+ "'"
				+ notebean.getText()
				+ "'"
				+ ","
				+ "'"
				+ userid
				+ "'"
				+ ","
				+ "'"
				+ bizid
				+ "'"
				+ "," + "'" + clientid + "'" + "," + "'" + thisDate + "'" + ")";

		int noteid = 0;
		try {
			past = conn.prepareStatement(sql1, Statement.RETURN_GENERATED_KEYS);
			past.executeUpdate(sql1);
			ResultSet rs = past.getGeneratedKeys();
			if (rs.next()) {
				noteid = rs.getInt(1);
			} else {
				return flag;
			}
			past.close();
			flag = true;

			if (filename != null) {
				String sql2 = "insert into attachment(noteid,title,date) values ("
						+ "'"
						+ noteid
						+ "'"
						+ ","
						+ "'"
						+ filename
						+ "'"
						+ ","
						+ "'" + thisDate + "'" + ")";
				past = conn.prepareStatement(sql2);
				past.executeUpdate(sql2);
				flag = true;
			}
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return flag;
	}

	public List ShowAllNote(BusinessBean business,int pagenow, int pagesize) {
		int notebizid = 0;
		if (business == null) {
			business = new BusinessBean();
			Map session = ActionContext.getContext().getSession();
			notebizid = (Integer) session.get("bizid");
			System.out.print(notebizid+"&");
			business.setBizid(notebizid);
			session.remove("bizid");
		} else {
			notebizid = business.getBizid();
		}

		List<NoteBean> list = new ArrayList();
		/*
		 * String sql = "select *,name from note,user where " +
		 * "user.userid = note.userid and note.bizid = " + "'" + notebizid +
		 * "'";
		 */
		int now=pagenow*pagesize;
		String sql = " select * from note where bizid="
				 + notebizid +" limit "+now+","+pagesize+"";
		Connection conn = db.getConnection();
		try {
			PreparedStatement past = conn.prepareStatement(sql);
			ResultSet rs = past.executeQuery();
			while (rs.next()) {
				NoteBean note = new NoteBean();
				note.setNoteid(rs.getInt("noteid"));
				int noteid = note.getNoteid();
				String sql2 = "select count(*) from attachment where noteid ="
						+ "'" + noteid + "'" + "group by noteid";
				try {
					Statement stmt = conn.createStatement();
					ResultSet rs2 = stmt.executeQuery(sql2);
					if (rs2.next()) {
						int attno = rs2.getInt(1);
						note.setAttno(attno);
					}

				} catch (SQLException e) {
					e.printStackTrace();
				}
				note.setUserid(Integer.parseInt(rs.getString("userid")));
				note.setBizid(notebizid);
				note.setClientid(Integer.parseInt(rs.getString("clientid")));
				note.setTitle(rs.getString("title"));
				String content=rs.getString("text");
				content=content.replace(" ", "&nbsp;");
				content=content.replace("\n", "<br/>");
				note.setText(content);
				String sql3="select user from user where userid="+"'"+rs.getString("userid")+"'";
				try {
					System.out.println(sql3);
					Statement stmt3 = conn.createStatement();
					ResultSet rs3 = stmt3.executeQuery(sql3);
					if (rs3.next()) {
						String name = rs3.getString(1);
						note.setName(name);
					}

				} catch (SQLException e) {
					e.printStackTrace();
				}
				//note.setName(rs.getString("userid"));
				note.setDate(rs.getString("date"));
				list.add(note);
				System.out.println(notebizid);
			}
			rs.close();
			past.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return list;
	}

	public NoteBean ShowOneNote(NoteBean notebean) {
		NoteBean note = new NoteBean();
		String sql = "select * from note where noteid = ? ";
		Connection conn = db.getConnection();
		PreparedStatement past = null;
		try {
			past = conn.prepareStatement(sql);
			past.setInt(1, notebean.getNoteid());
			ResultSet rs = past.executeQuery();
			if (!rs.next()) {
				return null;
			}
			note.setNoteid(Integer.parseInt(rs.getString("noteid")));
			note.setTitle(rs.getString("title"));
			note.setText(rs.getString("text"));
			rs.close();
			past.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			db.closeConnection(conn);
		}
		return note;
	}
}
